<?php

namespace Home\Service;

use Home\Common\FIdConst;
use Home\DAO\BizlogDAO;

require __DIR__ . '/../Common/Excel/PHPExcel/IOFactory.php';

/**
 * 库存建账Service
 *
 * @author 李静波
 */
class InitInventoryService extends PSIBaseService {
	private $LOG_CATEGORY = "库存建账";

	/**
	 * 获得仓库列表
	 */
	public function warehouseList() {
		if ($this->isNotOnline()) {
			return $this->emptyResult();
		}
		
		$sql = "select id, code, name, inited from t_warehouse ";
		$queryParams = array();
		
		$ds = new DataOrgService();
		$rs = $ds->buildSQL(FIdConst::INVENTORY_INIT, "t_warehouse");
		if ($rs) {
			$sql .= " where " . $rs[0];
			$queryParams = $rs[1];
		}
		
		$sql .= "order by code";
		
		return M()->query($sql, $queryParams);
	}

	/**
	 * 某个仓库的建账信息
	 */
	public function initInfoList($params) {
		if ($this->isNotOnline()) {
			return $this->emptyResult();
		}
		
		$warehouseId = $params["warehouseId"];
		$page = $params["page"];
		$start = $params["start"];
		$limit = $params["limit"];
		
		$db = M();
		$sql = "select v.id, g.code, g.name, g.spec, v.balance_count, v.balance_price, 
				v.balance_money, u.name as unit_name, v.biz_date,
				v.begin_dt, v.end_dt, v.expiration
				from t_inventory_detail_lot v, t_goods g, t_goods_unit u 
				where v.goods_id = g.id and g.unit_id = u.id and v.warehouse_id = '%s' 
				and ref_type = '库存建账' 
				order by g.code 
				limit %d, %d ";
		$data = $db->query($sql, $warehouseId, $start, $limit);
		$result = array();
		foreach ( $data as $i => $v ) {
			$result[$i]["id"] = $v["id"];
			$result[$i]["goodsCode"] = $v["code"];
			$result[$i]["goodsName"] = $v["name"];
			$result[$i]["goodsSpec"] = $v["spec"];
			$result[$i]["goodsCount"] = $v["balance_count"];
			$result[$i]["goodsUnit"] = $v["unit_name"];
			$result[$i]["goodsMoney"] = $v["balance_money"];
			$result[$i]["goodsPrice"] = $v["balance_price"];
			$result[$i]["initDate"] = $this->toYMD($v["biz_date"]);
			
			$qcBeginDT = $this->toYMD($v["begin_dt"]);
			if ($qcBeginDT && $qcBeginDT != "1970-01-01") {
				$result[$i]["qcBeginDT"] = $qcBeginDT;
			}
			$qcEndDT = $this->toYMD($v["end_dt"]);
			if ($qcEndDT && $qcEndDT != "1970-01-01") {
				$result[$i]["qcEndDT"] = $qcEndDT;
			}
			$expiration = $v["expiration"];
			if ($expiration > 0) {
				$result[$i]["expiration"] = $expiration;
			}
		}
		
		$sql = "select count(*) as cnt from t_inventory_detail_lot 
				where warehouse_id = '%s' and ref_type = '库存建账' ";
		$data = $db->query($sql, $warehouseId);
		
		return array(
				"initInfoList" => $result,
				"totalCount" => $data[0]["cnt"]
		);
	}

	/**
	 * 获得商品分类列表
	 */
	public function goodsCategoryList() {
		if ($this->isNotOnline()) {
			return $this->emptyResult();
		}
		
		$sql = "select id, code, name, full_name from t_goods_category ";
		$queryParams = array();
		
		$ds = new DataOrgService();
		$rs = $ds->buildSQL(FIdConst::INVENTORY_INIT, "t_goods_category");
		if ($rs) {
			$sql .= " where " . $rs[0];
			$queryParams = $rs[1];
		}
		
		$sql .= " order by code";
		
		$result = array();
		$db = M();
		$data = $db->query($sql, $queryParams);
		foreach ( $data as $i => $v ) {
			$result[$i]["id"] = $v["id"];
			$result[$i]["code"] = $v["code"];
			$fullName = $v["full_name"];
			if (! $fullName) {
				$fullName = $v["name"];
			}
			$result[$i]["name"] = $fullName;
		}
		
		return $result;
	}

	/**
	 * 获得某个分类下的商品列表
	 */
	public function goodsList($params) {
		if ($this->isNotOnline()) {
			return $this->emptyResult();
		}
		
		$warehouseId = $params["warehouseId"];
		$categoryId = $params["categoryId"];
		$page = $params["page"];
		$start = $params["start"];
		$limit = $params["limit"];
		
		$db = M();
		$sql = "select g.id, g.code, g.name, g.spec, v.balance_count, v.balance_price, 
				v.balance_money, u.name as unit_name, v.biz_date 
				from t_goods g inner join t_goods_unit u 
				on g.unit_id = u.id and g.category_id = '%s' 
				left join t_inventory_detail v
				on g.id = v.goods_id and v.ref_type = '库存建账' 
				and v.warehouse_id = '%s' 
				order by g.code 
				limit " . $start . ", " . $limit;
		$data = $db->query($sql, $categoryId, $warehouseId);
		$result = array();
		foreach ( $data as $i => $v ) {
			$result[$i]["id"] = $v["id"];
			$result[$i]["goodsCode"] = $v["code"];
			$result[$i]["goodsName"] = $v["name"];
			$result[$i]["goodsSpec"] = $v["spec"];
			$result[$i]["goodsCount"] = $v["balance_count"];
			$result[$i]["unitName"] = $v["unit_name"];
			$result[$i]["goodsMoney"] = $v["balance_money"];
			$result[$i]["goodsPrice"] = $v["balance_price"];
			$result[$i]["initDate"] = $v["biz_date"];
		}
		
		$sql = "select count(*) as cnt from t_goods where category_id = '%s' ";
		$data = $db->query($sql, $categoryId);
		
		return array(
				"goodsList" => $result,
				"totalCount" => $data[0]["cnt"]
		);
	}

	/**
	 * 提交建账信息
	 */
	public function commitInitInventoryGoods($params) {
		if ($this->isNotOnline()) {
			return $this->notOnlineError();
		}
		
		$warehouseId = $params["warehouseId"];
		$goodsId = $params["goodsId"];
		$goodsCount = intval($params["goodsCount"]);
		$goodsMoney = floatval($params["goodsMoney"]);
		
		if ($goodsCount <= 0) {
			return $this->bad("期初数量不能小于0");
		}
		
		if ($goodsMoney < 0) {
			return $this->bad("期初金额不能为负数");
		}
		
		$goodsPrice = $goodsMoney / $goodsCount;
		
		$db = M();
		$db->startTrans();
		
		$sql = "select name, inited from t_warehouse where id = '%s' ";
		$data = $db->query($sql, $warehouseId);
		if (! $data) {
			$db->rollback();
			return $this->bad("仓库不存在");
		}
		if ($data[0]["inited"] != 0) {
			$db->rollback();
			return $this->bad("仓库 [{$data[0]["name"]}] 已经建账完成，不能再次建账");
		}
		
		$sql = "select name from t_goods where id = '%s' ";
		$data = $db->query($sql, $goodsId);
		if (! $data) {
			$db->rollback();
			return $this->bad("商品不存在");
		}
		$sql = "select count(*) as cnt from t_inventory_detail 
				where warehouse_id = '%s' and goods_id = '%s' and ref_type <> '库存建账' ";
		$data = $db->query($sql, $warehouseId, $goodsId);
		$cnt = $data[0]["cnt"];
		if ($cnt > 0) {
			$db->rollback();
			return $this->bad("当前商品已经有业务发生，不能再建账");
		}
		
		$us = new UserService();
		$dataOrg = $us->getLoginUserDataOrg();
		
		// 总账
		$sql = "select id from t_inventory where warehouse_id = '%s' and goods_id = '%s' ";
		$data = $db->query($sql, $warehouseId, $goodsId);
		if (! $data) {
			$sql = "insert into t_inventory (warehouse_id, goods_id, in_count, in_price, 
						in_money, balance_count, balance_price, balance_money, data_org) 
						values ('%s', '%s', %d, %f, %f, %d, %f, %f, '%s') ";
			$rc = $db->execute($sql, $warehouseId, $goodsId, $goodsCount, $goodsPrice, $goodsMoney, 
					$goodsCount, $goodsPrice, $goodsMoney, $dataOrg);
			if ($rc === false) {
				$db->rollback();
				return $this->sqlError(__LINE__);
			}
		} else {
			$id = $data[0]["id"];
			$sql = "update t_inventory  
						set in_count = %d, in_price = %f, in_money = %f, 
						balance_count = %d, balance_price = %f, balance_money = %f 
						where id = %d ";
			$rc = $db->execute($sql, $goodsCount, $goodsPrice, $goodsMoney, $goodsCount, 
					$goodsPrice, $goodsMoney, $id);
			if ($rc === false) {
				$db->rollback();
				return $this->sqlError(__LINE__);
			}
		}
		
		// 明细账
		$sql = "select id from t_inventory_detail  
					where warehouse_id = '%s' and goods_id = '%s' and ref_type = '库存建账' ";
		$data = $db->query($sql, $warehouseId, $goodsId);
		if (! $data) {
			$sql = "insert into t_inventory_detail (warehouse_id, goods_id,  in_count, in_price,
						in_money, balance_count, balance_price, balance_money,
						biz_date, biz_user_id, date_created,  ref_number, ref_type, data_org)
						values ('%s', '%s', %d, %f, %f, %d, %f, %f, curdate(), '%s', now(), '', '库存建账', '%s')";
			$rc = $db->execute($sql, $warehouseId, $goodsId, $goodsCount, $goodsPrice, $goodsMoney, 
					$goodsCount, $goodsPrice, $goodsMoney, $us->getLoginUserId(), $dataOrg);
			if ($rc === false) {
				$db->rollback();
				return $this->sqlError(__LINE__);
			}
		} else {
			$id = $data[0]["id"];
			$sql = "update t_inventory_detail 
						set in_count = %d, in_price = %f, in_money = %f,
						balance_count = %d, balance_price = %f, balance_money = %f,
						biz_date = curdate()  
						where id = %d ";
			$rc = $db->execute($sql, $goodsCount, $goodsPrice, $goodsMoney, $goodsCount, 
					$goodsPrice, $goodsMoney, $id);
			if ($rc === false) {
				$db->rollback();
				return $this->sqlError(__LINE__);
			}
		}
		
		$db->commit();
		
		return $this->ok();
	}

	/**
	 * 完成建账
	 */
	public function finish($params) {
		if ($this->isNotOnline()) {
			return $this->notOnlineError();
		}
		
		$warehouseId = $params["warehouseId"];
		
		$db = M();
		$db->startTrans();
		
		$sql = "select name, inited from t_warehouse where id = '%s' ";
		$data = $db->query($sql, $warehouseId);
		if (! $data) {
			$db->rollback();
			return $this->bad("仓库不存在");
		}
		$inited = $data[0]["inited"];
		$name = $data[0]["name"];
		if ($inited == 1) {
			$db->rollback();
			return $this->bad("仓库 [{$name}] 已经建账完毕");
		}
		
		$sql = "update t_warehouse set inited = 1 where id = '%s' ";
		$rc = $db->execute($sql, $warehouseId);
		if ($rc === false) {
			$db->rollback();
			return $this->sqlError(__LINE__);
		}
		
		$sql = "update t_inventory_detail set biz_date = curdate() 
					where warehouse_id = '%s' and ref_type = '库存建账' ";
		$rc = $db->execute($sql, $warehouseId);
		if ($rc === false) {
			$db->rollback();
			return $this->sqlError(__LINE__);
		}
		
		$log = "仓库 [{$name}] 建账完毕";
		$bs = new BizlogService();
		$bs->insertBizlog($log, $this->LOG_CATEGORY);
		
		$db->commit();
		
		return $this->ok();
	}

	/**
	 * 取消库存建账标志
	 */
	public function cancel($params) {
		if ($this->isNotOnline()) {
			return $this->notOnlineError();
		}
		
		$warehouseId = $params["warehouseId"];
		
		$db = M();
		$db->startTrans();
		
		$sql = "select name, inited from t_warehouse where id = '%s' ";
		$data = $db->query($sql, $warehouseId);
		if (! $data) {
			$db->rollback();
			return $this->bad("仓库不存在");
		}
		$inited = $data[0]["inited"];
		$name = $data[0]["name"];
		if ($inited != 1) {
			$db->rollback();
			return $this->bad("仓库 [{$name}] 还没有标记为建账完毕，无需取消建账标志");
		}
		$sql = "select count(*) as cnt from t_inventory_detail 
				where warehouse_id = '%s' and ref_type <> '库存建账' ";
		$data = $db->query($sql, $warehouseId);
		$cnt = $data[0]["cnt"];
		if ($cnt > 0) {
			$db->rollback();
			return $this->bad("仓库 [{$name}] 中已经发生出入库业务，不能再取消建账标志");
		}
		
		$sql = "update t_warehouse set inited = 0 where id = '%s' ";
		$rc = $db->execute($sql, $warehouseId);
		if ($rc === false) {
			$db->rollback();
			return $this->sqlError(__LINE__);
		}
		
		$log = "仓库 [{$name}] 取消建账完毕标志";
		$bs = new BizlogService();
		$bs->insertBizlog($log, $this->LOG_CATEGORY);
		
		$db->commit();
		
		return $this->ok();
	}

	/**
	 * 新增或编辑建账信息
	 */
	public function editInitInv($params) {
		if ($this->isNotOnline()) {
			return $this->notOnlineError();
		}
		
		$warehouseId = $params["warehouseId"];
		$goodsId = $params["goodsId"];
		$qcBeginDT = $params["qcBeginDT"];
		if (! $qcBeginDT) {
			$qcBeginDT = "1970-01-01";
		}
		$expiration = $params["expiration"];
		$qcEndDT = date("Y-m-d", strtotime($qcBeginDT . " +$expiration day"));
		$goodsCount = intval($params["goodsCount"]);
		$goodsMoney = floatval($params["goodsMoney"]);
		
		if ($goodsCount < 0) {
			return $this->bad("期初数量不能为负数");
		}
		
		if ($goodsMoney < 0) {
			return $this->bad("期初金额不能为负数");
		}
		
		$db = M();
		$db->startTrans();
		
		$sql = "select name, inited from t_warehouse where id = '%s' ";
		$data = $db->query($sql, $warehouseId);
		if (! $data) {
			$db->rollback();
			return $this->bad("仓库不存在");
		}
		if ($data[0]["inited"] != 0) {
			$db->rollback();
			return $this->bad("仓库 [{$data[0]["name"]}] 已经建账完成，不能再次建账");
		}
		
		$sql = "select name from t_goods where id = '%s' ";
		$data = $db->query($sql, $goodsId);
		if (! $data) {
			$db->rollback();
			return $this->bad("商品不存在");
		}
		
		$sql = "select count(*) as cnt from t_inventory_detail_lot
				where warehouse_id = '%s' and goods_id = '%s' and ref_type <> '库存建账' ";
		$data = $db->query($sql, $warehouseId, $goodsId);
		$cnt = $data[0]["cnt"];
		if ($cnt > 0) {
			$db->rollback();
			return $this->bad("当前商品已经有业务发生，不能再建账");
		}
		
		if ($goodsCount == 0) {
			// 当输入商品数量为0的时候，就清除建账信息
			$sql = "delete from t_inventory_lot 
					where warehouse_id = '%s' and goods_id = '%s' 
						and begin_dt = '%s' and expiration = %d";
			$rc = $db->execute($sql, $warehouseId, $goodsId, $qcBeginDT, $expiration);
			if ($rc === false) {
				$db->rollback();
				return $this->sqlError(__LINE__);
			}
			
			$sql = "delete from t_inventory_detail_lot 
					where warehouse_id = '%s' and goods_id = '%s' 
						and begin_dt = '%s' and expiration = %d";
			$rc = $db->execute($sql, $warehouseId, $goodsId, $qcBeginDT, $expiration);
			if ($rc === false) {
				$db->rollback();
				return $this->sqlError(__LINE__);
			}
			
			$db->commit();
			
			return $this->ok();
		}
		
		$goodsPrice = $goodsMoney / $goodsCount;
		
		$us = new UserService();
		$dataOrg = $us->getLoginUserDataOrg();
		
		// 总账
		$sql = "select id from t_inventory_lot 
				where warehouse_id = '%s' and goods_id = '%s' 
					and begin_dt = '%s' and expiration = %d";
		$data = $db->query($sql, $warehouseId, $goodsId, $qcBeginDT, $expiration);
		if (! $data) {
			$sql = "insert into t_inventory_lot (warehouse_id, goods_id, in_count, in_price,
						in_money, balance_count, balance_price, balance_money, data_org,
						begin_dt, expiration, end_dt)
						values ('%s', '%s', %d, %f, %f, %d, %f, %f, '%s', '%s', %d, '%s') ";
			$rc = $db->execute($sql, $warehouseId, $goodsId, $goodsCount, $goodsPrice, $goodsMoney, 
					$goodsCount, $goodsPrice, $goodsMoney, $dataOrg, $qcBeginDT, $expiration, 
					$qcEndDT);
			if ($rc === false) {
				$db->rollback();
				return $this->sqlError(__LINE__);
			}
		} else {
			$id = $data[0]["id"];
			$sql = "update t_inventory_lot
						set in_count = %d, in_price = %f, in_money = %f,
						balance_count = %d, balance_price = %f, balance_money = %f
						where id = %d ";
			$rc = $db->execute($sql, $goodsCount, $goodsPrice, $goodsMoney, $goodsCount, 
					$goodsPrice, $goodsMoney, $id);
			if ($rc === false) {
				$db->rollback();
				return $this->sqlError(__LINE__);
			}
		}
		
		// 明细账
		$sql = "select id from t_inventory_detail_lot
					where warehouse_id = '%s' and goods_id = '%s' and ref_type = '库存建账' 
						and begin_dt = '%s' and expiration = %d ";
		$data = $db->query($sql, $warehouseId, $goodsId, $qcBeginDT, $expiration);
		if (! $data) {
			$sql = "insert into t_inventory_detail_lot (warehouse_id, goods_id,  in_count, in_price,
						in_money, balance_count, balance_price, balance_money,
						biz_date, biz_user_id, date_created,  ref_number, ref_type, data_org,
						begin_dt, expiration, end_dt)
						values ('%s', '%s', %d, %f, %f, %d, %f, %f, curdate(), '%s', now(), '', '库存建账', '%s',
						'%s', %d, '%s')";
			$rc = $db->execute($sql, $warehouseId, $goodsId, $goodsCount, $goodsPrice, $goodsMoney, 
					$goodsCount, $goodsPrice, $goodsMoney, $us->getLoginUserId(), $dataOrg, 
					$qcBeginDT, $expiration, $qcEndDT);
			if ($rc === false) {
				$db->rollback();
				return $this->sqlError(__LINE__);
			}
		} else {
			$id = $data[0]["id"];
			$sql = "update t_inventory_detail_lot
						set in_count = %d, in_price = %f, in_money = %f,
						balance_count = %d, balance_price = %f, balance_money = %f,
						biz_date = curdate()
						where id = %d ";
			$rc = $db->execute($sql, $goodsCount, $goodsPrice, $goodsMoney, $goodsCount, 
					$goodsPrice, $goodsMoney, $id);
			if ($rc === false) {
				$db->rollback();
				return $this->sqlError(__LINE__);
			}
		}
		
		$db->commit();
		
		return $this->ok();
	}

	/**
	 * 清空建账数据
	 */
	public function deleteInitData($params) {
		if ($this->isNotOnline()) {
			return $this->notOnlineError();
		}
		
		$warehouseId = $params["warehouseId"];
		
		$db = M();
		$db->startTrans();
		
		$sql = "select name, inited from t_warehouse
				where id = '%s' ";
		$data = $db->query($sql, $warehouseId);
		if (! $data) {
			$db->rollback();
			
			return $this->bad("仓库不存在");
		}
		
		$warehouseName = $data[0]["name"];
		$inited = $data[0]["inited"];
		if ($inited != 0) {
			$db->rollback();
			return $this->bad("仓库[$warehouseName]已经完成建账，不能再清空建账数据");
		}
		
		$sql = "delete from t_inventory_detail_lot
				where warehouse_id = '%s' ";
		$rc = $db->execute($sql, $warehouseId);
		if ($rc === false) {
			$db->rollback();
			return $this->sqlError(__LINE__);
		}
		
		$sql = "delete from t_inventory_lot
				where warehouse_id = '%s' ";
		$rc = $db->execute($sql, $warehouseId);
		if ($rc === false) {
			$db->rollback();
			return $this->sqlError(__LINE__);
		}
		
		$log = "清空仓库[$warehouseName]的建账数据";
		$bd = new BizlogDAO($db);
		$bd->insertBizlog($log, $this->LOG_CATEGORY);
		
		$db->commit();
		
		return $this->ok();
	}

	public function queryData($params) {
		if ($this->isNotOnline()) {
			return $this->notOnlineError();
		}
		
		$warehouseId = $params["warehouseId"];
		$goodsId = $params["goodsId"];
		$qcBeginDT = $params["qcBeginDT"];
		if (! $qcBeginDT) {
			$qcBeginDT = "1970-01-01";
		}
		$expiration = $params["expiration"];
		if (! $expiration) {
			$expiration = 0;
		}
		$result = array();
		
		$sql = "select balance_count, balance_money
				from t_inventory_lot
				where warehouse_id = '%s' and goods_id = '%s'
					and begin_dt = '%s' and expiration = %d";
		$db = M();
		$data = $db->query($sql, $warehouseId, $goodsId, $qcBeginDT, $expiration);
		if (! $data) {
			$result["success"] = false;
			return $result;
		}
		
		$result["success"] = true;
		$result["count"] = $data[0]["balance_count"];
		$result["money"] = $data[0]["balance_money"];
		
		return $result;
	}

	public function excel($warehouseId) {
		$db = M();
		$sql = "select name from t_warehouse where id = '%s' ";
		$data = $db->query($sql, $warehouseId);
		if (! $data) {
			return;
		}
		
		$warehouseName = $data[0]["name"];
		
		$sql = "select v.id, g.code, g.name, g.spec, v.balance_count, v.balance_price,
				v.balance_money, u.name as unit_name, v.biz_date,
				v.begin_dt, v.end_dt, v.expiration
				from t_inventory_detail_lot v, t_goods g, t_goods_unit u
				where v.goods_id = g.id and g.unit_id = u.id and v.warehouse_id = '%s'
				and ref_type = '库存建账'
				order by g.code
				";
		$data = $db->query($sql, $warehouseId);
		$result = array();
		foreach ( $data as $i => $v ) {
			$result[$i]["id"] = $v["id"];
			$result[$i]["goodsCode"] = $v["code"];
			$result[$i]["goodsName"] = $v["name"];
			$result[$i]["goodsSpec"] = $v["spec"];
			$result[$i]["goodsCount"] = $v["balance_count"];
			$result[$i]["goodsUnit"] = $v["unit_name"];
			$result[$i]["goodsMoney"] = $v["balance_money"];
			$result[$i]["goodsPrice"] = $v["balance_price"];
			$result[$i]["initDate"] = $this->toYMD($v["biz_date"]);
			
			$qcBeginDT = $this->toYMD($v["begin_dt"]);
			if ($qcBeginDT && $qcBeginDT != "1970-01-01") {
				$result[$i]["qcBeginDT"] = $qcBeginDT;
			}
			$qcEndDT = $this->toYMD($v["end_dt"]);
			if ($qcEndDT && $qcEndDT != "1970-01-01") {
				$result[$i]["qcEndDT"] = $qcEndDT;
			}
			$expiration = $v["expiration"];
			if ($expiration > 0) {
				$result[$i]["expiration"] = $expiration;
			}
		}
		
		// 导出Excel
		$excel = new \PHPExcel();
		
		$sheet = $excel->getActiveSheet();
		if (! $sheet) {
			$sheet = $excel->createSheet();
		}
		
		$sheet->setTitle($warehouseName);
		
		$sheet->getRowDimension('1')->setRowHeight(22);
		$sheet->setCellValue("A1", $warehouseName . " - 建账数据(导出时间: " . date("Y-m-d H:i:s") . ")");
		
		$sheet->getColumnDimension('A')->setWidth(15);
		$sheet->setCellValue("A2", "商品编码");
		
		$sheet->getColumnDimension('B')->setWidth(40);
		$sheet->setCellValue("B2", "品名");
		
		$sheet->getColumnDimension('C')->setWidth(20);
		$sheet->setCellValue("C2", "规格型号");
		
		$sheet->getColumnDimension('D')->setWidth(15);
		$sheet->setCellValue("D2", "生产日期");
		
		$sheet->getColumnDimension('E')->setWidth(15);
		$sheet->setCellValue("E2", "保质期(天)");
		
		$sheet->getColumnDimension('F')->setWidth(15);
		$sheet->setCellValue("F2", "到期日期");
		
		$sheet->getColumnDimension('G')->setWidth(15);
		$sheet->setCellValue("G2", "期初数量");
		
		$sheet->getColumnDimension('H')->setWidth(10);
		$sheet->setCellValue("H2", "单位");
		
		$sheet->getColumnDimension('I')->setWidth(15);
		$sheet->setCellValue("I2", "期初金额");
		
		$sheet->getColumnDimension('J')->setWidth(15);
		$sheet->setCellValue("J2", "期初单价");
		
		$sheet->getColumnDimension('K')->setWidth(15);
		$sheet->setCellValue("K2", "建账日期");
		
		foreach ( $result as $i => $v ) {
			$row = $i + 3;
			$sheet->setCellValue("A" . $row, $v["goodsCode"]);
			$sheet->setCellValue("B" . $row, $v["goodsName"]);
			$sheet->setCellValue("C" . $row, $v["goodsSpec"]);
			$sheet->setCellValue("D" . $row, $v["qcBeginDT"]);
			$sheet->setCellValue("E" . $row, $v["expiration"]);
			$sheet->setCellValue("F" . $row, $v["qcEndDT"]);
			$sheet->setCellValue("G" . $row, $v["goodsCount"]);
			$sheet->setCellValue("H" . $row, $v["goodsUnit"]);
			$sheet->setCellValue("I" . $row, $v["goodsMoney"]);
			$sheet->setCellValue("J" . $row, $v["goodsPrice"]);
			$sheet->setCellValue("K" . $row, $v["initDate"]);
		}
		
		// 画表格边框
		$styleArray = array(
				'borders' => array(
						'allborders' => array(
								'style' => 'thin'
						)
				)
		);
		$lastRow = count($result) + 2;
		$sheet->getStyle('A2:K' . $lastRow)->applyFromArray($styleArray);
		
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="' . $warehouseName . '.xls"');
		header('Cache-Control: max-age=0');
		
		$writer = \PHPExcel_IOFactory::createWriter($excel, "Excel5");
		$writer->save("php://output");
	}
}